﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Windows;
using TheDrinksApp.Models;
namespace TheDrinksApp.Database
{
    public static class Util
    {
        #region CSDL
        public static ObservableCollection<CatergoryDAO> GetAllCatergory()
        {
            ObservableCollection<CatergoryDAO> _catergoryEntries = new ObservableCollection<CatergoryDAO>();
            //retrieve dat
            string strSelect = "SELECT * FROM Category";
            _catergoryEntries = (Application.Current as App).db.SelectObservableCollection<CatergoryDAO>(strSelect);
            foreach (CatergoryDAO category in _catergoryEntries)
            {
                category.Catergory = category.Catergory.ToUpper();
            }
            return _catergoryEntries;
        }
        public static ObservableCollection<CatergoryDAO> GetCategoryByID( int id)
        {
            ObservableCollection<CatergoryDAO> _catergoryEntries = new ObservableCollection<CatergoryDAO>();
            //retrieve dat
            string strSelect = "SELECT * FROM Category where CatergoryID=" +id;
            _catergoryEntries = (Application.Current as App).db.SelectObservableCollection<CatergoryDAO>(strSelect);
            foreach (CatergoryDAO category in _catergoryEntries)
            {
                category.Catergory = category.Catergory.ToUpper();
            }
            return _catergoryEntries;
        }
        public static ObservableCollection<Drink> GetDrinksByCatergoryID( int id)
        {
            if (id == -1)
            {
                return GetAllDrink();
            }
            else
            {
                ObservableCollection<Drink> list = new ObservableCollection<Drink>();
                string strSelect = "SELECT * FROM Drink Where CatergoryID=" + id;
                list = (Application.Current as App).db.SelectObservableCollection<Drink>(strSelect);
                return list;
            }
        }
        public static ObservableCollection<Drink> GetAllDrink()
        {
            ObservableCollection<Drink> list = new ObservableCollection<Drink>();
            string strSelect = "SELECT * FROM Drink";
            list = (Application.Current as App).db.SelectObservableCollection<Drink>(strSelect);
            return list;
        }
        public static ObservableCollection<Mixer> GetAllMixer()
        {
            ObservableCollection<Mixer> list = new ObservableCollection<Mixer>();
            string strSelect = "SELECT * FROM Mixer";
            list = (Application.Current as App).db.SelectObservableCollection<Mixer>(strSelect);
            return list;
        }
        public static ObservableCollection<Order> GetAllOrder()
        {
            ObservableCollection<Order> list = new ObservableCollection<Order>();
            string strSelect = "SELECT * FROM 'Order'";
            list = (Application.Current as App).db.SelectObservableCollection<Order>(strSelect);
            return list;
        }
        public static ObservableCollection<OrderItem> GetAllOrderItemByOrderID( int id )
        {
            ObservableCollection<OrderItem> list = new ObservableCollection<OrderItem>();
            //retrieve dat
            string strSelect = "SELECT * FROM OrderedItems WHERE OrderID =" + id;
            list = (Application.Current as App).db.SelectObservableCollection<OrderItem>(strSelect);
            return list;
        }
        public static CurrentOrderDAO ConvertOderItemToCurrentOrderDAO(OrderItem order)
        {
            CurrentOrderDAO current = new CurrentOrderDAO();
            current.drink = GetDrinkById(order.DrinkID);
            current.ice = order.Ice;
            current.lemon = order.Lemon;
            current.mixer = GetMixerById(order.MixerID);
            current.order = GetOrderById(order.OrderID);
            current.orderItemId = order.OrderdItemID;
            current.quantity = order.QTY;
            current.size = GetSizeById(order.SizeID);
            return current;
        }
        public static OrderItem ConvertCurrentOrderDAOToOrderItem(CurrentOrderDAO currentorder)
        {
            OrderItem orderItem = new OrderItem();
            orderItem.DrinkID = currentorder.drink.DrinkID;
            orderItem.Ice = currentorder.ice;
            orderItem.Lemon = currentorder.lemon;
            orderItem.MixerID = currentorder.mixer.MixerID;
            orderItem.QTY = currentorder.quantity;
            orderItem.SizeID = currentorder.size.SizeID;
            return orderItem;
        }
        public static ObservableCollection<OrderItem> GetAllOrderItem()
        {
            ObservableCollection<OrderItem> list = new ObservableCollection<OrderItem>();
            //retrieve dat
            string strSelect = "SELECT * FROM OrderedItems";
            list = (Application.Current as App).db.SelectObservableCollection<OrderItem>(strSelect);
            return list;
        }
        public static ObservableCollection<ObjectSize> GetAllSize()
        {
            ObservableCollection<ObjectSize> list = new ObservableCollection<ObjectSize>();
            string strSelect = "SELECT * FROM Size";
            list = (Application.Current as App).db.SelectObservableCollection<ObjectSize>(strSelect);
            return list;
        }
        public static int SaveOrder(Order order)
        {
            string cmd = "Insert into 'Order' ( OrderDate, OrderName, HowMuch) Values (\""+ order.OrderDate 
                                                    + "\",\"" + order.OrderName + "\"," + order.HowMuch +")";
            return (Application.Current as App).db.Execute(cmd);

        }
        public static int SavetOrderItem(OrderItem orderItem)
        {
            ObservableCollection<OrderItem> list = new ObservableCollection<OrderItem>();
            list = (Application.Current as App).db.SelectObservableCollection<OrderItem>("SELECT * from OrderedItems order by ROWID DESC limit 1");
            string cmd = "Insert into OrderedItems (DrinkID, MixerID, SizeID, OrderID, QTY, Ice, Lemon, OrderdItemID ) Values("
                              +orderItem.DrinkID.ToString()+","
                              + orderItem.MixerID.ToString() + ","
                              +orderItem.SizeID.ToString()+","
                              + orderItem.OrderID.ToString() + ","
                              + orderItem.QTY.ToString()+","
                              + (orderItem.Ice ? "1":"0")+","
                              + (orderItem.Lemon ? "1" : "0") + ","
                              + ((list.Count > 0 ) ? (list[0].OrderdItemID +1).ToString() : (0).ToString())  + ")";
            return (Application.Current as App).db.Execute(cmd);
        }
        public static bool UpdateOrder(Order order)
        {
            string cmd = "Update 'Order' Set HowMuch = "+ order.HowMuch +", OrderName=\"" + order.OrderName + "\" Where OrderID =" + order.OrderID;
            return ((Application.Current as App).db.Execute(cmd) == 0);
        }
        public static ObjectSize GetSizeByName(string sizeName)
        {
            ObservableCollection<ObjectSize> list = new ObservableCollection<ObjectSize>();
            string strSelect = "SELECT * FROM Size Where UPPER(SizeName)=\"" + sizeName.ToUpper() + "\"";
            list = (Application.Current as App).db.SelectObservableCollection<ObjectSize>(strSelect);
            return (list.Count > 0) ? list[0] : (new ObjectSize());
        }
        public static Mixer GetMixerByName(string mixerName)
        {
            ObservableCollection<Mixer> list = new ObservableCollection<Mixer>();
            string strSelect = "SELECT * FROM Mixer Where UPPER(MixerName)=\"" + mixerName.ToUpper() + "\"";
            list = (Application.Current as App).db.SelectObservableCollection<Mixer>(strSelect);
            return (list.Count > 0) ? list[0] : (new Mixer());
        }
        public static ObjectSize GetSizeById(int id)
        {
            ObservableCollection<ObjectSize> list = new ObservableCollection<ObjectSize>();
            string strSelect = "SELECT * FROM Size Where SizeID=" + id ;
            list = (Application.Current as App).db.SelectObservableCollection<ObjectSize>(strSelect);
            return (list.Count > 0) ? list[0] : (new ObjectSize());
        }
        public static Mixer GetMixerById(int id)
        {
            ObservableCollection<Mixer> list = new ObservableCollection<Mixer>();
            string strSelect = "SELECT * FROM Mixer Where MixerID=" + id;
            list = (Application.Current as App).db.SelectObservableCollection<Mixer>(strSelect);
            return (list.Count > 0) ? list[0] : (new Mixer());
        }
        public static Order GetOrderById(int id)
        {
            ObservableCollection<Order> list = new ObservableCollection<Order>();
            string strSelect = "SELECT * FROM 'Order' Where OrderID=" + id;
            list = (Application.Current as App).db.SelectObservableCollection<Order>(strSelect);
            return (list.Count > 0) ? list[0] : (new Order());
        }
        public static Drink GetDrinkById(int id)
        {
            ObservableCollection<Drink> list = new ObservableCollection<Drink>();
            string strSelect = "SELECT * FROM Drink Where DrinkID=" + id;
            list = (Application.Current as App).db.SelectObservableCollection<Drink>(strSelect);
            return (list.Count > 0) ? list[0] : ( new Drink());
        }
        public static Drink GetDrinkByName(string name)
        {
            ObservableCollection<Drink> list = new ObservableCollection<Drink>();
            string strSelect = "SELECT * FROM Drink Where UPPER(DrinkName) =\"" + name.ToUpper()  +"\"";
            list = (Application.Current as App).db.SelectObservableCollection<Drink>(strSelect);
            return (list.Count > 0) ? list[0] : (new Drink());
        }
        public static bool DeleteOrderWithId(int id)
        {
            string cmd = "Delete From 'Order' Where OrderId=" + id;
            (Application.Current as App).db.Execute(cmd);
            return Util.DeleteOrderItemWithOrderId(id);
        }
        public static bool DeleteOrderItemWithId(int id)
        {
            string cmd = "Delete From OrderedItems Where OrderdItemId=" + id;
            return ((Application.Current as App).db.Execute(cmd) == 0);
        }
        public static bool DeleteOrderItemWithOrderId(int orderid)
        {
            string cmd = "Delete From OrderedItems Where OrderId=" + orderid;
            return ((Application.Current as App).db.Execute(cmd) == 0 );
        }
        #endregion
        public static int type;
        public static PAGE preDetailPage;
        public static int goFromRecentOrCurrent;
        public static Order orderEditting;
        private static float howMuchRecent = 0;

        public static float HowMuchRecent
        {
            get { return Util.howMuchRecent; }
            set { Util.howMuchRecent = value; }
        }

        private static float howMuch = -1;

        public static float HowMuch
        {
            get { return Util.howMuch; }
            set { Util.howMuch = value; }
        }

        private static List<CurrentOrderDAO> _orderItems;

        public static List<CurrentOrderDAO> orderItems
        {
            get {
                if (Util._orderItems == null)
                    Util._orderItems = new List<CurrentOrderDAO>();
                return Util._orderItems; 
            }
            set { Util._orderItems = value; }
        }
        private static List<CurrentOrderDAO> _recentOrders;

        public static List<CurrentOrderDAO> recentOrders
        {
            get
            {
                if (Util._recentOrders == null)
                    Util._recentOrders = new List<CurrentOrderDAO>();
                return Util._recentOrders;
            }
            set { Util._recentOrders = value; }
        }
        private static List<CurrentOrderDAO> _currentOrders;

        public static List<CurrentOrderDAO> currentOrders
        {
            get {
                if (Util._currentOrders == null)
                    Util._currentOrders = new List<CurrentOrderDAO>();
                return Util._currentOrders; 
            }
            set { Util._currentOrders = value; }
        }
        private static CurrentOrderDAO _currentOrder;

        public static CurrentOrderDAO currentOrder
        {
            get {
                if (Util._currentOrder == null)
                    Util._currentOrder = new CurrentOrderDAO();
                return Util._currentOrder; 
            }
            set { Util._currentOrder = value; }
        }
        public static bool AddToRecentOrderWithTotal( float total ){
            Order order = new Order();
            order.HowMuch = total;
            order.OrderDate = ConvertDate();
            order.OrderName = ConvertDate();
            int id = SaveOrder(order);
            ObservableCollection<Order> list = new ObservableCollection<Order>();
            list = (Application.Current as App).db.SelectObservableCollection<Order>("SELECT * from 'Order' order by ROWID DESC limit 1");
            if (list.Count > 0)
            {
                id = list[0].OrderID;
            }
            foreach (CurrentOrderDAO currentorder in currentOrders)
            {
                OrderItem _order = ConvertCurrentOrderDAOToOrderItem(currentorder);
                _order.OrderID = id;
                SavetOrderItem(_order);
            }
            currentOrders.Clear();
            return false;
        }
        public static string ConvertDate()
        {
            return DateTime.Now.ToString("dd.MM.yy / HH.mm");
        }
        public static void RefreshListCurrentOrders()
        {
            for (int i = currentOrders.Count-1; i >=0; i--)
            {
                if (currentOrders[i].quantity == 0)
                {
                    currentOrders.RemoveAt(i);
                }
            }
        }
        public static bool AddToCurrentOrders()
        {
            for (int i = 0; i < orderItems.Count; i++)
            {
                if (orderItems[i].quantity > 0)
                {
                    bool isExist = false;
                    foreach (CurrentOrderDAO order in currentOrders)
                    {
                        if (order.drink.DrinkID == orderItems[i].drink.DrinkID && order.size.SizeID == orderItems[i].size.SizeID
                            && order.mixer.MixerID == orderItems[i].mixer.MixerID && order.ice == orderItems[i].ice && order.lemon == orderItems[i].lemon)
                        {
                            order.quantity = order.quantity + orderItems[i].quantity;
                            isExist = true;
                            break;
                        }
                    }
                    if ( isExist == false )
                        currentOrders.Add(orderItems[i]);
                }
            }
            orderItems.Clear();
            return false;
        }
        public static string GetCurrencySymbol()
        {
            NumberFormatInfo nfi = CultureInfo.CurrentCulture.NumberFormat;
            return nfi.CurrencySymbol;
        }
        public static CurrentOrderDAO nowEditting; 
    }
    public static class GlobalVariable
    {
        public static string SOFT_DRINKS = "SOFT DRINKS";
        public static string BEERS_AND_ALES = "BEERS AND ALES";
        public static string WINES = "WINES";
        public static string SPIRITS = "SPIRITS";
        public static string COCKTAILS = "COCKTAILS";
        public static MainPage mainPage;
        public static int version = 0; //0 free, 1: full
    }
}
